﻿using System.Data;
using System.Data.SqlClient;

namespace UniversityStudentManagemnetSystem.DAO.DAL.Gateway
{
    public class ViewGateway:DAO.Gateway.Gateway
    {

                          //For First View(Department,Semester)
        public DataTable GetAllScheduleCourseForDepartmentAndSemesterForView(int selectedDepartment, int selectedSemester)
        {
            string selectQuery =
                string.Format(
                    "select * from Final_Course_Schedule_ViewForCoding_7 where DepartmentId ='" + selectedDepartment + "' and SemesterId = '" + selectedSemester + "'");
            SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(selectQuery,GetConnectionString());
            DataSet myDataset=new DataSet();
            sqlDataAdapter.Fill(myDataset);
            DataTable dataTable = myDataset.Tables[0];
            int rowCount = dataTable.Rows.Count;
            return dataTable;
        }
        //For First View(Department,Semester)
        public DataTable GetAllScheduleCoursesForSelectedDepartment(int department)
        {
            string selectQuery = string.Format("select * from Final_Course_Schedule_ViewForCoding_7 where DepartmentId ='" + department + "'");
            SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(selectQuery,GetConnectionString());
            DataSet dataSet=new DataSet();
            sqlDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            int rowCount = dataTable.Rows.Count;
            return dataTable;

        }

                             //For 2nd View 8 no Page
        public DataTable GetAllUnAssignCoursesForDepartmentForView(int selectedDepartment)
        {
            string selectQuery = string.Format("SELECT * FROM view_UnAssignedCourse8 WHERE DepartmentId = '" + selectedDepartment + "' AND TeacherId IS NULL");

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet aDataSet = new DataSet();
            sqlDataAdapter.Fill(aDataSet);
            DataTable mDataTable = aDataSet.Tables[0];
            return mDataTable;
        }

                             //For 2nd View 8 no Page
        public DataTable GetAllUnAssignCoursesForSemesterAndDepartment(int selectSemester, int departmentId)
        {
            string selectQuery = string.Format("SELECT * FROM view_UnAssignedCourse8 WHERE DepartmentId = '" + departmentId + "' AND SemesterId='" + selectSemester + "'  AND TeacherId IS NULL");

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet aDataSet = new DataSet();
            sqlDataAdapter.Fill(aDataSet);
            DataTable mDataTable = aDataSet.Tables[0];
            return mDataTable;
        }

                           //For 3rd View 9 no Page
        public DataTable GetUnscheduleCourseForDepartmentId(int selectedDepartment)
        {
            string selectQuery = string.Format("select * from Final_UnScheduleCourse_View_9 where DepartmentId='" + selectedDepartment + "' AND Flag Is Null");
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet myDataSet = new DataSet();
            adapter.Fill(myDataSet);
            DataTable myDatable = myDataSet.Tables[0];
            int rowCount = myDatable.Rows.Count;
            return myDatable;
        }

                         //For 3rd View 9 no Page
        public DataTable GetAllUnscheduleCourseForDepartmentAndSemester(int department, int semester)
        {
            string selectQuery = string.Format("select * from Final_UnScheduleCourse_View_9 where DepartmentId='" + department + "' AND SemesterId='" + semester + "' AND Flag Is Null");
            SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet myDataSet = new DataSet();
            adapter.Fill(myDataSet);
            DataTable myDatable = myDataSet.Tables[0];
            
            return myDatable;
        }

    }
}